package com.blog.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.blog.VO.PlateVO;
import com.blog.VO.PostVO;
import com.blog.dao.BaseDao;
import com.blog.dao.MidTableDao;
import com.blog.dao.MultilistDao;
import com.blog.entity.User;

public class MultilistDaoImpl implements MultilistDao {
	MidTableDao midTableDao = new MidTableDaoImpl();

	@Override
	public List<User> selectUserLikeByUserId(int userId, int pageNum, int pageSize) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		String sql = "SELECT DISTINCT t1.* FROM user t1 INNER JOIN userlike t2 ON t1.userId = t2.userId WHERE t1.userId IN (SELECT likeId FROM userlike WHERE userId = ?);";
		List<User> userList = new ArrayList<User>();
		User selectUser = null;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,userId);
			res = ps.executeQuery();
			while(res.next()) {
				selectUser = new User();
				SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				selectUser.setUserId(res.getInt("userId"));
				selectUser.setUserName(res.getString("userName"));
				selectUser.setUserPass(res.getString("userPass"));
				selectUser.setUserSex(res.getString("userSex"));
				Date dt1 = null;
				try {
					dt1 = format.parse(format.format(res.getTimestamp("userBirth")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectUser.setUserBirth(dt1);
				selectUser.setUserPhone(res.getString("userPhone"));
				selectUser.setUserEmail(res.getString("userEmail"));
				selectUser.setIndividualResume(res.getString("individualResume"));
				selectUser.setUserHeadImg(res.getString("userHeadImg"));
				selectUser.setExperience(res.getInt("experience"));
				selectUser.setUserState(res.getInt("userState"));
				Date dt2 = null;
				try {
					dt2 = format.parse(format.format(res.getTimestamp("regDate")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectUser.setRegDate(dt2);
				userList.add(selectUser);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return userList;
	}
	
	@Override
	public List<User> selectUserFansByLikeId(int likeId, int pageNum, int pageSize) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		String sql = "SELECT DISTINCT t1.* FROM user t1 INNER JOIN userlike t2 ON t1.userId = t2.userId WHERE t1.userId IN (SELECT userId FROM userlike WHERE likeId = ?);";
		List<User> userList = new ArrayList<User>();
		User selectUser = null;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,likeId);
			res = ps.executeQuery();
			while(res.next()) {
				selectUser = new User();
				SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				selectUser.setUserId(res.getInt("userId"));
				selectUser.setUserName(res.getString("userName"));
				selectUser.setUserPass(res.getString("userPass"));
				selectUser.setUserSex(res.getString("userSex"));
				Date dt1 = null;
				try {
					dt1 = format.parse(format.format(res.getTimestamp("userBirth")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectUser.setUserBirth(dt1);
				selectUser.setUserPhone(res.getString("userPhone"));
				selectUser.setUserEmail(res.getString("userEmail"));
				selectUser.setIndividualResume(res.getString("individualResume"));
				selectUser.setUserHeadImg(res.getString("userHeadImg"));
				selectUser.setExperience(res.getInt("experience"));
				selectUser.setUserState(res.getInt("userState"));
				Date dt2 = null;
				try {
					dt2 = format.parse(format.format(res.getTimestamp("regDate")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectUser.setRegDate(dt2);
				userList.add(selectUser);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return userList;
	}

	@Override
	public int selectPlateAllStarByPlateId(int plateId) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		String sql = "SELECT COUNT(1) AS plateStarNum FROM allpost t1 INNER JOIN poststar t2 ON t1.postId = t2.postId WHERE postPlateId = ?;";
		int plateStarNum = 0;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,plateId);
			res = ps.executeQuery();
			if(res.next()) {
				plateStarNum = res.getInt("plateStarNum");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return plateStarNum;
	}

	@Override
	public int selectPlateAllLikeByPlateId(int plateId) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		String sql = "SELECT COUNT(1) AS plateLikeNum FROM allpost t1 INNER JOIN postlike t2 ON t1.postId = t2.postId WHERE postPlateId = ?;";
		int plateLikeNum = 0;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,plateId);
			res = ps.executeQuery();
			if(res.next()) {
				plateLikeNum = res.getInt("plateLikeNum");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return plateLikeNum;
	}
	
	@Override
	public List<PlateVO> selectBestStarPlateByNum(int num) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<PlateVO> plateVOList = new ArrayList<PlateVO>();
		String sql = "SELECT t1.*,COUNT(t1.plateId) AS plateStarNum FROM allplate t1 LEFT JOIN allpost t2 ON t1.plateId = t2.postPlateId LEFT JOIN poststar t3 ON t2.postId = t3.postId GROUP BY t1.plateId ORDER BY plateStarNum DESC LIMIT 0,?;";
		PlateVO selectPlateVO = null;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql.toString());
			ps.setInt(1,num);
			res = ps.executeQuery();
			while(res.next()) {
				selectPlateVO = new PlateVO();
				selectPlateVO.setPlateId(res.getInt("plateId"));
				selectPlateVO.setPlateName(res.getString("plateName"));
				selectPlateVO.setPlateIconImg(res.getString("plateIconImg"));
				selectPlateVO.setPlateState(res.getInt("plateState"));
				SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				Date dt = null;
				try {
					dt = format.parse(format.format(res.getTimestamp("regDate")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectPlateVO.setRegDate(dt);
				selectPlateVO.setAllStarPostNum(res.getInt("plateStarNum"));
				selectPlateVO.setAllLikePostNum(this.selectPlateAllLikeByPlateId(res.getInt("plateId")));
				plateVOList.add(selectPlateVO);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return plateVOList;
	}

	@Override
	public List<PlateVO> selectBestLikePlateByNum(int num) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<PlateVO> plateVOList = new ArrayList<PlateVO>();
		String sql = "SELECT t1.*,COUNT(t1.plateId) AS plateLikeNum FROM allplate t1 LEFT JOIN allpost t2 ON t1.plateId = t2.postPlateId LEFT JOIN postlike t3 ON t2.postId = t3.postId GROUP BY t1.plateId ORDER BY plateLikeNum DESC LIMIT 0,?;";
		PlateVO selectPlateVO = null;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql.toString());
			ps.setInt(1,num);
			res = ps.executeQuery();
			while(res.next()) {
				selectPlateVO = new PlateVO();
				selectPlateVO.setPlateId(res.getInt("plateId"));
				selectPlateVO.setPlateName(res.getString("plateName"));
				selectPlateVO.setPlateIconImg(res.getString("plateIconImg"));
				selectPlateVO.setPlateState(res.getInt("plateState"));
				SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				Date dt = null;
				try {
					dt = format.parse(format.format(res.getTimestamp("regDate")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectPlateVO.setRegDate(dt);
				selectPlateVO.setAllStarPostNum(this.selectPlateAllStarByPlateId(res.getInt("plateId")));
				selectPlateVO.setAllLikePostNum(res.getInt("plateLikeNum"));
				plateVOList.add(selectPlateVO);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return plateVOList;
	}
	
	@Override
	public List<PostVO> selectBestStarPostByNum(int num) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<PostVO> postVOList = new ArrayList<PostVO>();
		String sql = "SELECT t1.*,COUNT(t2.postId) AS starNum FROM allpost t1 LEFT JOIN poststar t2 ON t1.postId = t2.postId GROUP BY t1.postId ORDER BY starNum DESC LIMIT 0,?;";
		PostVO selectPostVO = null;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,num);
			res = ps.executeQuery();
			while(res.next()) {
				selectPostVO = new PostVO();
				selectPostVO.setPostId(res.getInt("postId"));
				selectPostVO.setPostTitle(res.getString("postTitle"));
				selectPostVO.setPostText(res.getString("postText"));
				selectPostVO.setPostUserId(res.getInt("postUserId"));
				selectPostVO.setPostId(res.getInt("postId"));
				selectPostVO.setPostState(res.getInt("postState"));
				SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				Date dt = null;
				try {
					dt = format.parse(format.format(res.getTimestamp("creationTime")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectPostVO.setCreationTime(dt);
				selectPostVO.setStarNum(res.getInt("starNum"));
				selectPostVO.setLikeNum(midTableDao.selectPostlikeNumByPostId(res.getInt("postId")));
				postVOList.add(selectPostVO);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return postVOList;
	}

	@Override
	public List<PostVO> selectBestLikePostByNum(int num) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<PostVO> postVOList = new ArrayList<PostVO>();
		String sql = "SELECT t1.*,COUNT(t2.postId) AS likeNum FROM allpost t1 LEFT JOIN postlike t2 ON t1.postId = t2.postId GROUP BY t1.postId ORDER BY likeNum DESC LIMIT 0,?;";
		PostVO selectPostVO = null;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,num);
			res = ps.executeQuery();
			while(res.next()) {
				selectPostVO = new PostVO();
				selectPostVO.setPostId(res.getInt("postId"));
				selectPostVO.setPostTitle(res.getString("postTitle"));
				selectPostVO.setPostText(res.getString("postText"));
				selectPostVO.setPostUserId(res.getInt("postUserId"));
				selectPostVO.setPostId(res.getInt("postId"));
				selectPostVO.setPostState(res.getInt("postState"));
				SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				Date dt = null;
				try {
					dt = format.parse(format.format(res.getTimestamp("creationTime")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectPostVO.setCreationTime(dt);
				selectPostVO.setLikeNum(res.getInt("likeNum"));
				selectPostVO.setStarNum(midTableDao.selectPostStarNumByPostId(res.getInt("postId")));
				postVOList.add(selectPostVO);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return postVOList;
	}
}
